﻿using CCWin;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace TelephoneYiShou.XQL
{
    public partial class FrmSaleGoods : Skin_Mac
    {
        public string loginname;
        public DataTable dt;
        public FrmSaleGoods(string name)
        {
            InitializeComponent();
            loginname = name;
        }
        int i, start;//i表示总行数，start表示当前位置
        int size = 9;//size表示一页显示的数量
        string ssql;
        string tablename;
        private void FrmSaleGoods_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“telephoneYiShouDataSet.Commodity”中。您可以根据需要移动或删除它。
            this.commodityTableAdapter.Fill(this.telephoneYiShouDataSet.Commodity);
            //查询订单表
            ssql = "select orderid '订单编号',c.CommodityName '手机型号',colour '颜色',cl.ClientName '客户名',e.EmployeeName '操作员',Number '销售数量',OrderTime '出售时间',Paytype '付款方式',Moneysum '本单金额',Remark '备注' from SellGoods s,Commodity c,Client cl,Employee e where s.CommodityID=c.CommodityID and s.ClientID = cl.ClientID and s.Employeeid = e.EmployeeID ";
            dt = DBHelper.GetDataTable(ssql);
            i = dt.Rows.Count;//获取数据的条数
            tablename = "[SellGoods]";
            show(0, size, ssql);
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
            //查询数据库中操作员的名字
            string sssql = "select * from employee";
            DataTable dts = DBHelper.GetDataTable(sssql);
            for (int i = 0; i < dts.Rows.Count; i++)
            {
                txtempid.Items.Add(dts.Rows[i][1]);
            }
            //显示登陆账户的编号
            string sqll = string.Format("select employeeid from employee where employeename='{0}'",loginname);
            DataTable logindt = DBHelper.GetDataTable(sqll);
            loginid.Text = "当前登陆的姓名为:"+loginname+"            登录的员工编号为"+ logindt.Rows[0][0].ToString();
            //商品名称绑定
            string sqlname = "select distinct commodityname from commodity";
            DataTable dte = DBHelper.GetDataTable(sqlname);
            for (int i = 0; i < dte.Rows.Count; i++)
            {
                txtgoodsname.Items.Add(dte.Rows[i][0]);
            }
            //顾客姓名绑定
            string sqlclname = "select distinct clientname from client";
            DataTable cldt = DBHelper.GetDataTable(sqlclname);
            for (int i = 0; i < cldt.Rows.Count; i++)
            {
                txtsellname.Items.Add(cldt.Rows[i][0]);
            }
        }
        //添加
        private void buttadd_Click(object sender, EventArgs e)
        {
            string goodsid = null; 
            string sellid = null;
            string empid = null;
            //查询商品编号
            if (!string.IsNullOrEmpty(txtgoodsname.Text))
            {
                string sql = string.Format("select commodityid from commodity where commodityname='{0}'", txtgoodsname.Text);
                goodsid = DBHelper.GetDataTable(sql).Rows[0][0].ToString();
            }
            //查询客户编号
            if (!string.IsNullOrEmpty(txtsellname.Text))
            {
                string sql = string.Format("select clientid from client where clientname='{0}'", txtsellname.Text);
                sellid = DBHelper.GetDataTable(sql).Rows[0][0].ToString();
            }
            //查询操作员的d编号
            if (!string.IsNullOrEmpty(txtempid.Text))
            {
                string sql = string.Format("select employeeid from employee where employeename='{0}'", txtempid.Text);
                DataTable dtss = DBHelper.GetDataTable(sql);//执行
                empid = dtss.Rows[0][0].ToString();
            }
            string sum = txtsum.Text;
            string date = txtdate.Text;
            string money = txtmoney.Text;
            string fufei = txtfangshi.Text;
            string beizhu = txtbeizhu.Text;
            if (string.IsNullOrEmpty(date))
            {
                date = DateTime.Now.ToString();
            }
            if (string.IsNullOrEmpty(goodsid)|| string.IsNullOrEmpty(sellid) || string.IsNullOrEmpty(empid) || string.IsNullOrEmpty(sum) || string.IsNullOrEmpty(date) || string.IsNullOrEmpty(money) || string.IsNullOrEmpty(fufei) || string.IsNullOrEmpty(beizhu) )
            {
                MessageBoxEx.Show("请填写完整的数据");
            }
            else
            {
                string sumsql = string.Format("select sum from stock where commodityid = '{0}'", goodsid);
                string isql = string.Format("insert into sellgoods values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",goodsid,sellid,sum,date,fufei,money,beizhu,empid );//增加语句
                string usql = string.Format("update stock set sum = sum-{0} where commodityid = {1}", sum, goodsid);//修改语句
                DataTable dtsss = DBHelper.GetDataTable(sumsql);
                int sumcount = int.Parse(dtsss.Rows[0][0].ToString());
                if (sumcount>int.Parse(sum))
                {
                    if (DBHelper.ExecuteNonQuery(isql))
                    {
                        DBHelper.ExecuteNonQuery(usql);//减少库存的数量
                        MessageBoxEx.Show("添加成功");
                        start = 0;
                        dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
                        ssql = "select orderid '订单编号',c.CommodityName '手机型号',colour '颜色',cl.ClientName '客户名',e.EmployeeName '操作员',Number '销售数量',OrderTime '出售时间',Paytype '付款方式',Moneysum '本单金额',Remark '备注' from SellGoods s,Commodity c,Client cl,Employee e where s.CommodityID=c.CommodityID and s.ClientID = cl.ClientID and s.Employeeid = e.EmployeeID ";
                        dt = DBHelper.GetDataTable(ssql);
                        i = dt.Rows.Count;//获取数据的条数
                        tablename = "SellGoods";
                        show(0, size, ssql);
                        labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
                                                                                                           //清空数据
                        txtsum.Text = null;
                        txtbeizhu.Text = null;
                        txtmoney.Text = null;
                        txtdate.Text = null;
                        txtfangshi.Text = null;
                        txtgoodsname.Text = null;
                        txtsellname.Text = null;
                    }
                    else
                    {
                        MessageBoxEx.Show("添加失败");
                    }
                }
                else
                {
                    MessageBoxEx.Show("库存不足");
                }
                
            }
        }
        //客户表
        private void button1_Click(object sender, EventArgs e)
        {
            start = 0;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            ssql = "select clientid '客户编号',clientname '客户名',gender '性别',phone '手机号' ,Adderss '住址' from client";
            dt = DBHelper.GetDataTable(ssql);
            i = dt.Rows.Count;//获取数据的条数
            tablename = "[client]";
            show(0, size, ssql);
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }
        //商品表
        private void button2_Click(object sender, EventArgs e)
        {
            start = 0;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
            ssql = "select c.commodityid '商品编号',CommodityName '商品名称',s.[sum] '商品数量',t.TypeName '商品品牌',OrderGoodsPrice '进货价',SellGoodsPrice '出售价',introduce '介绍',su.SupplierName '供应商',Colour '颜色'  from commodity c,Stock s ,CommodityType t ,Supplier su where c.CommodityID=s.CommodityID and c.TypeID=t.TypeID and c.SupplierID=su.SupplierID";
            dt = DBHelper.GetDataTable(ssql);
            i = dt.Rows.Count;//获取数据的条数
            tablename = "[Commodity]";
            show(0, size, ssql);
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }
        //出售订单表
        private void button3_Click(object sender, EventArgs e)
        {
            start = 0;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
            ssql = "select orderid '订单编号',c.CommodityName '手机型号',colour '颜色',cl.ClientName '客户名',e.EmployeeName '操作员',Number '销售数量',OrderTime '出售时间',Paytype '付款方式',Moneysum '本单金额',Remark '备注' from SellGoods s,Commodity c,Client cl,Employee e where s.CommodityID=c.CommodityID and s.ClientID = cl.ClientID and s.Employeeid = e.EmployeeID";
            dt = DBHelper.GetDataTable(ssql);
            i = dt.Rows.Count;//获取数据的条数
            tablename = "SellGoods";
            show(0, size, ssql);
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }
        //下一页
        private void buttnextpage_Click(object sender, EventArgs e)
        {
            if (start + 1 > i / size)
            {
                MessageBoxEx.Show("已是尾页");
            }
            else
            {
                start = start + 1;
                show(start * size, size,ssql);
            }
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }
        //上一页
        private void buttpreviouspage_Click(object sender, EventArgs e)
        {
            if (start < 1)
            {
                MessageBoxEx.Show("已是第一页");
            }
            else
            {
                start = start - 1;
                show(start * size, size,ssql);
            }
            labelcount.Text = string.Format("第{0}页/共{1}页 共{2}条数据", start + 1, i / size + 1, i);//显示数据
        }
        //清空数据
        private void buttexit_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        //获得焦点本单金额框
        //获取颜色
        private void skinComboBox1_MouseEnter(object sender, EventArgs e)
        {
            //颜色数据绑定
            txtcolor.Items.Clear();
            string sqlcolor = string.Format("select colour from commodity where commodityname = '{0}'", txtgoodsname.Text);
            DataTable dtes = DBHelper.GetDataTable(sqlcolor);
            for (int i = 0; i < dtes.Rows.Count; i++)
            {
                txtcolor.Items.Add(dtes.Rows[i][0]);
            }
        }

        private void txtmoney_MouseEnter(object sender, EventArgs e)
        {
            try
            {
                if (!string.IsNullOrEmpty(txtgoodsname.Text) && !string.IsNullOrEmpty(txtsum.Text) && !string.IsNullOrEmpty(txtcolor.Text))
                {
                    string idsql = string.Format("select commodityid from commodity where commodityname = '{0}' and colour = '{1}'", txtgoodsname.Text, txtcolor.Text);
                    string txtgoodsid = DBHelper.GetDataTable(idsql).Rows[0][0].ToString();
                    string ssmsql = string.Format("select sellgoodsprice from commodity where commodityid = '{0}'", txtgoodsid);
                    DataTable sumdt = DBHelper.GetDataTable(ssmsql);
                    double sums = double.Parse(sumdt.Rows[0][0].ToString()) * double.Parse(txtsum.Text);
                    txtmoney.Text = sums.ToString();
                }
                else
                {
                    txtmoney.Text = null;
                }
            }
            catch (Exception)
            {

                MessageBoxEx.Show("请查看你的输入是否错误");
            }
            
        }

        //只显示size条数据
        public void show(int i, int j,string str)
        {
            SqlDataAdapter sqlada = DBHelper.GetDataTableFenYe(str);
            DataSet ds = new DataSet();
            sqlada.Fill(ds, i, j,tablename);//把数据库表中的i-j的记录数用来填充数据集
            dataGridView1.DataSource = ds.Tables[0];
            ds = null;//清空数据集
        }
    }
}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    